package com.xiaoshuidi.cloud.module.finance.job.bank;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.xiaoshuidi.cloud.framework.common.core.KeyValue;
import com.xiaoshuidi.cloud.module.finance.dal.dataobject.finance.FinanceBankDO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.function.Consumer;
import java.util.stream.Collectors;

@Slf4j
@Repository
public class BankRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;

    @DS("oracle")
    public void queryBankStream(Consumer<List<FinanceBankDO>> consumer) {
        jdbcTemplate.setFetchSize(500);
        jdbcTemplate.execute((ConnectionCallback<Object>) conn -> {
            PreparedStatement ps = conn.prepareStatement("select * from hfjt.view_bebank");
            ResultSet rs = ps.executeQuery();
            rs.setFetchSize(500);
            List<FinanceBankDO> list = new ArrayList<>(100);
            while (rs.next()) {
                String bankName = rs.getString("BKNAME");
                String bankCode = rs.getString("BKNUMBER");
                String bankProvince = rs.getString("BKPROV");
                String bankCity = rs.getString("BKCITY");
                list.add(new FinanceBankDO(bankName, bankCode, bankProvince, bankCity));
                if (list.size() >= 500) {
                    consumer.accept(list);
                    log.info("同步银行信息 {} 条", list.size());
                    list.clear();
                }
            }
            if (list.size() >= 0) {
                consumer.accept(list);
                log.info("同步银行信息 {} 条", list.size());
                list.clear();
            }
            rs.close();
            ps.close();
            return true;
        });
    }

    @DS("master")
    public void insertBank(List<FinanceBankDO> list) {
        jdbcTemplate.batchUpdate("insert into finance_bank(bank_name, bank_code, bank_province, bank_city, create_time)" + " values (?,?,?,?,now()) "
                + " on duplicate key update bank_name = values(bank_name), bank_city = values(bank_city), bank_province = values(bank_province), update_time = now()", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                FinanceBankDO bankDO = list.get(i);
                ps.setString(1, bankDO.getBankName());
                ps.setString(2, bankDO.getBankCode());
                ps.setString(3, bankDO.getBankProvince());
                ps.setString(4, bankDO.getBankCity());
            }

            @Override
            public int getBatchSize() {
                return list.size();
            }
        });
        Map<String, List<KeyValue<String, String>>> m = list.stream().map(b -> new KeyValue<>(b.getBankProvince(), b.getBankCity()))
                .collect(Collectors.groupingBy(KeyValue::getKey, LinkedHashMap::new, Collectors.toList()));

        saveProvince(m.keySet().toArray());

        SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from finance_bank_area where type = 1 and name in (" + StringUtils.repeat(",?", m.size()).substring(1) + ")",
                m.keySet().toArray());
        while (rs.next()) {
            String name = rs.getString("name");
            Integer id = rs.getInt("id");
            List<KeyValue<String, String>> v = m.get(name);
            m.remove(name);
            jdbcTemplate.batchUpdate("insert into finance_bank_area (name, type, parent_id, sort) values (?,?,?,0) on duplicate key update name = values(name)",
                    new BatchPreparedStatementSetter() {
                        @Override
                        public void setValues(PreparedStatement ps, int i) throws SQLException {
                            KeyValue<String, String> kv = v.get(i);
                            ps.setString(1, kv.getValue());
                            ps.setInt(2, 2);
                            ps.setInt(3, id);
                        }

                        @Override
                        public int getBatchSize() {
                            return v.size();
                        }
                    });
        }

        if (m.size() > 0) {
            saveProvince(m.keySet().toArray());
            List<Object[]> params = m.values().stream().flatMap(Collection::stream).map(k -> new Object[]{k.getValue(), k.getKey()}).collect(Collectors.toList());
            jdbcTemplate.batchUpdate("insert into finance_bank_area (name, type, parent_id, sort) values (?,2,(select id from finance_bank_area where name = ? and type = 1), 0) "
                    + "on duplicate key update name = values(name)", params);
        }
    }

    private void saveProvince(Object[] provinces) {
        jdbcTemplate.batchUpdate("insert into finance_bank_area (name, type, parent_id, sort) values (?,1,0,0) on duplicate key update name = values(name)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, provinces[i].toString());
            }

            @Override
            public int getBatchSize() {
                return provinces.length;
            }
        });
    }
}
